package com.example.ksxtxkty.service;

import java.sql.*;
import javax.sql.DataSource;
import org.apache.commons.dbcp2.BasicDataSource;
import org.springframework.stereotype.Service;

@Service
public class EnterpriseFinInfoBatchCreator {

    // 数据库连接配置
    private static final String DB_URL = "jdbc:mysql://19.15.31.234:3306/test";
    private static final String USER = "sharedb_test";
    private static final String PASS = "sharedb_ZKR789";

    // 批处理大小
    private static final int BATCH_SIZE = 50000;

    public static void tocreate() {
        DataSource dataSource = setupDataSource();

        try (Connection conn = dataSource.getConnection()) {
            // 禁用自动提交以进行批处理
            conn.setAutoCommit(false);

            // 创建目标表
            createTargetTable(conn);

            // 执行大数据量查询并插入
            executeLargeQuery(conn);

            System.out.println("表 T_ENTERPRISE_FIN_INFO_ZK 创建并填充完成");
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    private static void createTargetTable(Connection conn) throws SQLException {
        String createTableSQL = "CREATE TABLE T_ENTERPRISE_FIN_INFO_ZK (" +
                "id VARCHAR(255), " +
                "CREDIT_CODE VARCHAR(255), " +
                "COMPANY_NAME VARCHAR(255), " +
                "INVESTOR_CREDIT_CODE VARCHAR(255), " +
                "DISCLOSURE_DATE DATETIME, " +
                "ROUND VARCHAR(255), " +
                "TRANSACTION_AMOUNT DECIMAL(20,2), " +
                "INVESTOR_NAME VARCHAR(255), " +
                "IS_BUSINESS_ABNORMALITY VARCHAR(10), " +
                "SHAREHOLDING_RATIO DECIMAL(10,4), " +
                "UPDATED_DATE DATETIME, " +
                "DELETE_FLAG VARCHAR(1))";

        try (Statement stmt = conn.createStatement()) {
            stmt.execute(createTableSQL);
        }
    }

    private static void executeLargeQuery(Connection conn) throws SQLException {
        String selectSQL = "SELECT t1.id, t2.uniscid as CREDIT_CODE, t2.entname as COMPANY_NAME, " +
                "t1.uniscid as INVESTOR_CREDIT_CODE, t2.abntime as DISCLOSURE_DATE, " +
                "null as ROUND, t1.subconam as TRANSACTION_AMOUNT, t1.inv as INVESTOR_NAME, " +
                "'是' AS IS_BUSINESS_ABNORMALITY, t1.subconprop as SHAREHOLDING_RATIO, " +
                "t1.instdate as UPDATED_DATE, '0' as DELETE_FLAG " +
                "FROM sharedb.investor t1 " +
                "INNER JOIN sharedb.ao_opanomaly t2 ON t1.cerno = t2.cerno";

        String insertSQL = "INSERT INTO T_ENTERPRISE_FIN_INFO_ZK VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";

        try (PreparedStatement pstmt = conn.prepareStatement(insertSQL);
             Statement stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY)) {

            // 设置获取大量数据时的优化参数
            stmt.setFetchSize(BATCH_SIZE);

            ResultSet rs = stmt.executeQuery(selectSQL);
            int count = 0;

            while (rs.next()) {
                pstmt.setString(1, rs.getString("id"));
                pstmt.setString(2, rs.getString("CREDIT_CODE"));
                pstmt.setString(3, rs.getString("COMPANY_NAME"));
                pstmt.setString(4, rs.getString("INVESTOR_CREDIT_CODE"));
                pstmt.setTimestamp(5, rs.getTimestamp("DISCLOSURE_DATE"));
                pstmt.setNull(6, Types.VARCHAR);
                pstmt.setBigDecimal(7, rs.getBigDecimal("TRANSACTION_AMOUNT"));
                pstmt.setString(8, rs.getString("INVESTOR_NAME"));
                pstmt.setString(9, rs.getString("IS_BUSINESS_ABNORMALITY"));
                pstmt.setBigDecimal(10, rs.getBigDecimal("SHAREHOLDING_RATIO"));
                pstmt.setTimestamp(11, rs.getTimestamp("UPDATED_DATE"));
                pstmt.setString(12, rs.getString("DELETE_FLAG"));

                pstmt.addBatch();

                if (++count % BATCH_SIZE == 0) {
                    pstmt.executeBatch();
                    conn.commit();
                    System.out.println("已处理 " + count + " 条记录");
                }
            }

            // 处理剩余记录
            pstmt.executeBatch();
            conn.commit();
            System.out.println("总共处理 " + count + " 条记录");
        }
    }

    private static DataSource setupDataSource() {
        BasicDataSource ds = new BasicDataSource();
        ds.setUrl(DB_URL);
        ds.setUsername(USER);
        ds.setPassword(PASS);
        ds.setInitialSize(5);  // 初始连接数
        ds.setMaxTotal(20);    // 最大连接数
        ds.setMaxIdle(10);     // 最大空闲连接数
        ds.setMinIdle(5);      // 最小空闲连接数
        ds.setMaxWaitMillis(10000); // 获取连接最大等待时间
        return ds;
    }
}

